﻿using System;
using System.Linq;
using System.Text;
using NDBGen.Descriptors.Columns.Tokens;
using NDBGen.Plugins.Database;

namespace NDBGen.Plugins
{
    [DatabaseScriptGenerator("SqlServer")]
    public class SqlServerScriptGenerator : FileGeneratorBase<DatabaseGeneratorPluginData>, IFileGenerator<DatabaseGeneratorPluginData>, IDatabaseScriptFileGenerator
    {
        public void WriteCreateSchema(String schemaName, IIndentedTextWriter writer)
        {
            writer.WriteLine(SqlSchemaResources.SchemaIfNotExistsFormat, schemaName);
            writer.WriteLine(SqlSchemaResources.SchemaCreateFormat, schemaName);
            writer.WriteLine(SqlResources.Go);
        }

        public void WriteCreateTable(Table table, IIndentedTextWriter writer)
        {
            writer.WriteLine(SqlSchemaResources.TableIfNotExistsFormat, table.Schema, table.Name);
            writer.WriteLine(SqlSchemaResources.TableCreateOpenFormat, table.Schema, table.Name);
            using (writer.Indent())
            {
                foreach (var column in table.Columns) this.GenerateColumn(column, writer);

                var pkColumns = table.Columns.Where(c => c.HasPrimaryKey).ToArray();
                if (pkColumns.Any())
                {
                    writer.WriteLine(SqlSchemaResources.TablePKConstraintFormat, table.Name);
                    using (writer.Indent())
                    {
                        foreach (var col in table.Columns.Where(c => c.HasPrimaryKey))
                        {
                            writer.WriteLine(SqlSchemaResources.TablePKColumnListFormat, col.Name);
                        }
                    }
                    writer.WriteLine(") with (");
                    using (writer.Indent())
                    {
                        writer.WriteLine(SqlSchemaResources.TablePKHints);
                    }
                    writer.WriteLine(") on [default]");
                }
            }
            writer.WriteLine(") on [default]");
            writer.WriteLine(SqlResources.Go);
        }

        internal void GenerateColumn(Column column, IIndentedTextWriter writer)
        {
            var dataTypeGenerator = this.GetGeneratorForDescriptor(column.DataTypeDescriptor);
            dataTypeGenerator.WriteSql(writer);

            var generators = 
                from descriptor in column.Descriptors
                let generator = this.GetGeneratorForDescriptor(descriptor)
                where generator != null
                select generator;

            foreach (var generator in generators.SortOrdered())
            {
                if (generator != generators.First()) writer.Write(' ');

                generator.WriteSql(writer);
            }

            writer.Write(',');
            writer.WriteLine();
        }

        public void WriteDropTable(Table table, IIndentedTextWriter writer)
        {
            writer.WriteLine(SqlSchemaResources.TableIfExistsFormat, table.Schema, table.Name);
            writer.WriteLine(SqlSchemaResources.TableDropFormat, table.Schema, table.Name);
            writer.WriteLine(SqlResources.Go);
        }

        public void WriteDefineData(String prefix, Table table, ScriptedTableData data, IIndentedTextWriter writer)
        {
            if (data == null || !data.Entries.Any(e => e.Values.Any())) return;
            else
            {
                var columns = (
                    from lc in data.Columns
                    join c in table.Columns on lc.Name equals c.Name into cols
                    from c in cols.DefaultIfEmpty()
                    select new
                    {
                        Name = lc.Name,
                        ParameterName = lc.ParameterName,
                        DataType = c == null ? default(DataType) : c.DataType,
                        TriggersBulkInsert = lc.TriggersBulkInsert,
                        BulkInsertSuffix = lc.BulkInsertSuffix,
                        RequiresValueLookup = lc.RequiresValueLookup,
                        ReferencedTableSchema = lc.ReferencedTableSchema,
                        ReferencedTableName = lc.ReferencedTableName,
                        ReferencedTableValueColumn = lc.ReferencedTableValueColumn,
                        Type = c == null ? DataType.NVarCharMax : c.DataType,
                        IsNullable = c == null ? false : c.Descriptors.Any(t => t.GetType() == typeof(NullableTokenDescriptor))
                    }).ToArray();

                var bulkInsertTrigger = columns.SingleOrDefault(c => c.TriggersBulkInsert);
                var bulkInsertSuffix = bulkInsertTrigger == null ? String.Empty : bulkInsertTrigger.BulkInsertSuffix;

                String[] previousValues = new String[data.Columns.Length];
                foreach (var entry in data.Entries)
                {
                    foreach (var convColumn in columns.Where(c => c.DataType.RequiresConvert()))
                    {
                        writer.WriteLine(SqlDataResources.DefineDeclareVariableFormat, convColumn.Name, entry.GetHashCode(), convColumn.DataType.ToString().ToLower());
                        writer.WriteLine(SqlDataResources.DefineSelectFormat, convColumn.Name, entry.GetHashCode(), entry.Values[Array.IndexOf(columns, convColumn)], convColumn.DataType.ToString().ToLower());
                    }

                    writer.Write(SqlDataResources.DefineExecSPNameFormat, table.Schema, table.Name.Depluralize(), prefix ?? String.Empty, bulkInsertSuffix);
                    var paramsWritten = 0;
                    for (var index = 0; index < columns.Length; index++)
                    {
                        if (index >= columns.Length) throw new ArgumentException(String.Format("The lookup data for {0}.{1} specifies more value indexes than columns", table.Schema, table.Name));
                        var column = columns[index];

                        var value = index < entry.Values.Length ? entry.Values[index] : (String)null;

                        if (column.TriggersBulkInsert) continue;

                        if (value == "^") value = previousValues[index];
                        else previousValues[index] = value;

                        if (column.Type == DataType.Bit)
                        {
                            var b = Boolean.Parse(value);
                            value = Convert.ToByte(b).ToString();
                        }

                        if (paramsWritten > 0) writer.Write(',');

                        writer.Write(SqlDataResources.DefineExecSPParamFormat, column.RequiresValueLookup ? column.ParameterName : column.Name);

                        if (String.IsNullOrEmpty(value))
                        {
                            if (column.IsNullable) writer.Write("null");
                            else if (column.Type.IsStringType()) writer.Write("''");
                            else writer.Write("0");
                        }
                        else
                        {
                            if (!column.DataType.RequiresConvert() && (column.RequiresValueLookup || column.Type.RequiresQuotes())) writer.Write("'");
                            if (column.DataType.RequiresConvert()) writer.Write("@{0}_{1}", column.Name, entry.GetHashCode());
                            else writer.Write(value.Replace("'", "''"));
                            if (!column.DataType.RequiresConvert() && (column.RequiresValueLookup || column.Type.RequiresQuotes())) writer.Write("'");
                            // note: column.DataType.ToString() will have to be replaced by some smarter logic if this needs to be done for any sized data type like varchar(50), etc
                        }
                        paramsWritten++;
                    }
                    writer.Write(';');
                    writer.WriteLine();
                }
                writer.WriteLine(SqlResources.Go);
            }
        }

        public void WriteDefineProcedure(String prefix, Table table, ScriptedTableData data, IIndentedTextWriter writer)
        {
            if (data == null || !data.Entries.Any(e => e.Values.Any()) || !data.Entries.Any(e => e.Values.Any(v => !String.IsNullOrEmpty(v)))) return;
            else
            {
                if (table.Columns == null) throw new NullReferenceException(String.Format("Table.Columns was null while generating SQL define{2} procedure for {0}.{1}", table.Schema, table.Name, prefix));
                if (data.Columns == null) throw new NullReferenceException(String.Format("data.Columns was null while generating SQL define{2} procedure for {0}.{1}", table.Schema, table.Name, prefix));

                var allColumns = (
                    from c in table.Columns
                    join lc in data.Columns on c.Name equals lc.Name into lookupColumns
                    from lc in lookupColumns.DefaultIfEmpty()
                    // this is grossly inefficient, but it's the easiest way to maintain the
                    // original order of data.Columns
                    orderby data.Columns.ToList().IndexOf(lc)
                    select new
                    {
                        Name = c.Name,
                        Length = c.Length,
                        Type = c.DataType,
                        Descriptors = c.Descriptors,
                        ScriptedData = lc,
                        DbType = String.Format("{0}{1}",
                            c.DataType.ToString().Replace("Max", "(max)").ToLower(),
                            c.Length.HasValue ? String.Format("({0})", c.Length) : String.Empty)
                    }).ToArray();

                if (allColumns.Count(c => c.ScriptedData != null && c.ScriptedData.TriggersBulkInsert) > 1) throw new InvalidOperationException(String.Format("Table {0}.{1} defines multiple columns that trigger a bulk insert operation. Only one such column is allowed.", table.Schema, table.Name));

                try
                {
                    var autoGeneratedIdColumn = allColumns.SingleOrDefault(c =>
                        c.Descriptors.Any(t => t.GetType() == typeof(PrimaryKeyTokenDescriptor)) &&
                        c.Descriptors.Any(t => t.GetType() == typeof(AutoGeneratedTokenDescriptor)));

                    var columns = allColumns.Where(c => c.ScriptedData != null && !c.Descriptors.Any(m => m.GetType() == typeof(AutoGeneratedTokenDescriptor))).ToArray();
                    var idColumn = columns.SingleOrDefault(c => c.Descriptors.Any(t => t.GetType() == typeof(PrimaryKeyTokenDescriptor)));
                    var lookupSourceColumns = columns.Where(c => c.ScriptedData != null && c.ScriptedData.IsLookupKey);
                    var nonAutoGeneratedIdColumns = columns.Where(c => c != autoGeneratedIdColumn).ToArray();
                    var bulkInsertTrigger = allColumns.SingleOrDefault(c => c.ScriptedData != null && c.ScriptedData.TriggersBulkInsert);
                    var bulkInsertSuffix = bulkInsertTrigger == null ? String.Empty : bulkInsertTrigger.ScriptedData.BulkInsertSuffix;

                    writer.WriteLine(SqlDataResources.SPIfNotExistsFormat, table.Schema, table.Name.Depluralize(), prefix ?? String.Empty, bulkInsertSuffix);
                    using (writer.Indent())
                    {
                        writer.WriteLine(SqlDataResources.SPStubFormat, table.Schema, table.Name.Depluralize(), prefix ?? String.Empty, bulkInsertSuffix);
                    }
                    writer.WriteLine(SqlResources.Go);
                    writer.WriteLine(SqlDataResources.SPAlterProcedureFormat, table.Schema, table.Name.Depluralize(), prefix ?? String.Empty, bulkInsertSuffix);
                    using (writer.Indent())
                    {
                        foreach (var column in nonAutoGeneratedIdColumns.Where(c => !c.ScriptedData.TriggersBulkInsert))
                        {
                            if (column.ScriptedData.RequiresValueLookup)
                            {
                                writer.Write(SqlDataResources.SPParamFormat, column.ScriptedData.ParameterName, column.ScriptedData.GetParameterType(table.Columns));
                            }
                            else if (!column.ScriptedData.TriggersBulkInsert)
                            {
                                writer.Write(SqlDataResources.SPParamFormat, column.ScriptedData.Name, column.DbType);
                            }
                            if (column != nonAutoGeneratedIdColumns.Last(c => !c.ScriptedData.TriggersBulkInsert)) writer.Write(",");
                            writer.WriteLine();
                        }
                    }
                    writer.WriteLine(")");
                    writer.WriteLine("as");
                    writer.WriteLine("begin");

                    using (writer.Indent())
                    {
                        foreach (var column in columns)
                        {
                            this.WriteRecordLookupSql(column.ScriptedData, column.DbType, writer);
                        }

                        if (autoGeneratedIdColumn == null && !lookupSourceColumns.Any())
                            throw new InvalidOperationException(String.Format("The table {0}.{1} must have an auto-generated ID or the the lookup definition must specify a lookup column.", table.Schema, table.Name));

                        if (bulkInsertTrigger == null && columns.Length > 1 && lookupSourceColumns.Any())
                        {
                            writer.Write(
                                "if not exists (select * from [{0}].[{1}] where", table.Schema, table.Name);
                            foreach (var lookupColumn in lookupSourceColumns)
                            {
                                if (lookupColumn != lookupSourceColumns.First())
                                {
                                    writer.Write(" and");
                                }

                                var isNullable = lookupColumn.Descriptors.Any(m => m.GetType() == typeof(NullableTokenDescriptor) && ((NullableTokenDescriptor)m).IsActive);
                                if (isNullable)
                                {
                                    writer.Write(" (([{0}] is null and @{0}{1} is null) or", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                }

                                writer.Write(" [{0}] = @{0}{1}", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);

                                if (isNullable)
                                {
                                    writer.Write(")");
                                }
                            }
                            writer.WriteLine(")");
                        }

                        writer.Write(String.Format(
                            "insert [{0}].[{1}] (", table.Schema, table.Name));
                        foreach (var column in columns)
                        {
                            writer.Write("[{0}]", column.Name);
                            if (column != columns.Last()) writer.Write(",");
                        }
                        writer.Write(")");
                        writer.WriteLine();
                        if (bulkInsertTrigger == null)
                        {
                            writer.Write(
                                "values (");
                            foreach (var column in columns)
                            {
                                writer.Write("@{0}{1}", column.Name, column.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                if (column != columns.Last()) writer.Write(",");
                            }
                            writer.Write(");");
                        }
                        else
                        {
                            var values = new StringBuilder();
                            foreach (var column in nonAutoGeneratedIdColumns)
                            {
                                if (column == bulkInsertTrigger)
                                {
                                    values.AppendFormat("[{0}]", column.ScriptedData.ReferencedTableValueColumn);
                                }
                                else
                                {
                                    values.AppendFormat("@{0}{1}", column.Name, column.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                }
                                if (column != nonAutoGeneratedIdColumns.Last()) values.AppendFormat(",");
                            }

                            var where = new StringBuilder();
                            if (lookupSourceColumns.Any())
                            {
                                var whereColumns = lookupSourceColumns.Where(c => c != bulkInsertTrigger);
                                where.Append(" where");
                                foreach (var lookupColumn in whereColumns)
                                {
                                    if (lookupColumn != whereColumns.First())
                                    {
                                        where.Append(" and");
                                    }

                                    var isNullable = lookupColumn.Descriptors.Any(m => m.GetType() == typeof(NullableTokenDescriptor) && ((NullableTokenDescriptor)m).IsActive);
                                    if (isNullable)
                                    {
                                        where.AppendFormat(" (([{0}] is null and @{0}{1} is null) or", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                    }

                                    where.AppendFormat(" [{0}] = @{0}{1}", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);

                                    if (isNullable)
                                    {
                                        where.AppendFormat(")");
                                    }
                                }
                            }
                            this.WriteBulkInsertSelectSql(bulkInsertTrigger.ScriptedData, values.ToString(), where.ToString(), table.Schema, table.Name, writer);
                        }
                        //if (columns.Length > 1 && lookupSourceColumns.Any()) writer.Indent--;
                        writer.WriteLine();

                        if (bulkInsertTrigger == null && columns.Length > 1 && lookupSourceColumns.Any() && columns.Any(c => !lookupSourceColumns.Contains(c)))
                        {
                            writer.WriteLine("else");
                            using (writer.Indent())
                            {
                                writer.WriteLine(
                                    "update [{0}].[{1}] set", table.Schema, table.Name);
                                using (writer.Indent())
                                {
                                    foreach (var column in columns)
                                    {
                                        if (!lookupSourceColumns.Contains(column))
                                        {
                                            writer.Write(
                                                "[{0}] = @{0}{1}", column.Name, column.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                            if (column != columns.Where(c => !lookupSourceColumns.Contains(c) && c != idColumn).Last())
                                                writer.Write(",");
                                            writer.WriteLine();
                                        }
                                    }
                                }
                                writer.Write(
                                    "where");
                                foreach (var lookupColumn in lookupSourceColumns)
                                {
                                    if (lookupColumn != lookupSourceColumns.First())
                                    {
                                        writer.Write(" and");
                                    }

                                    var isNullable = lookupColumn.Descriptors.Any(m => m.GetType() == typeof(NullableTokenDescriptor) && ((NullableTokenDescriptor)m).IsActive);
                                    if (isNullable)
                                    {
                                        writer.Write(" (([{0}] is null and @{0}{1} is null) or", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                    }

                                    writer.Write(" [{0}] = @{0}{1}", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);

                                    if (isNullable)
                                    {
                                        writer.Write(")");
                                    }
                                }
                                writer.WriteLine(";");
                            }
                        }

                        writer.WriteLine();
                        writer.WriteLine(
                            "return 0;");
                    }

                    //writer.Indent--;
                    writer.WriteLine(
                        "end");
                    writer.WriteLine(
                        "go");
                }
                catch (Exception ex)
                {
                    throw new Exception(String.Format("Error generating SQL define{2} procedure for {0}.{1}", table.Schema, table.Name, prefix), ex);
                }
            }            
        }

        void WriteBulkInsertSelectSql(ScriptedDataColumn column, String values, String where, String schemaName, String tableName, IIndentedTextWriter writer)
        {
            if (column.TriggersBulkInsert)
            {
                writer.WriteLine(SqlDataResources.SPBulkInsertSelectStatementFormat,
                        values,
                        column.ReferencedTableValueColumn,
                        column.ReferencedTableSchema,
                        column.ReferencedTableName,
                        column.Name,
                        schemaName,
                        tableName,
                        where);
            }
        }

        void WriteRecordLookupSql(ScriptedDataColumn column, String dbType, IIndentedTextWriter writer)
        {
            if (column.RequiresValueLookup)
            {
                writer.WriteLine(SqlDataResources.SPDeclareLookupVariableFormat, column.Name, dbType);
                writer.WriteLine(SqlDataResources.SPLookupSelectStatementFormat,
                        column.Name,
                        column.ReferencedTableValueColumn,
                        column.ReferencedTableSchema,
                        column.ReferencedTableName,
                        column.ReferencedTableLookupColumn,
                        column.ParameterName);
                writer.WriteLine();
            }
        }

        void IDatabaseScriptFileGenerator.Init(ParsedInput parsedInput)
        {
            var pluginData = new DatabaseGeneratorPluginData(parsedInput);
            this.Init(pluginData);
        }
    }
}